package com.huaxia.dao.excellent;

import com.huaxia.pojo.excellent.BranchMDRT;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/10 9:01
 */

public interface BranchMDRTMapper {

    @Select("select a.BRANCHSNAME,a.branchAllMDRT,b.allMDRT branchLastMDRT,a.branchAllMDRT-b.allMDRT branchIncreaseMDRT,ROUND(DECODE(b.allMDRT,0,-9999,(a.branchAllMDRT-b.allMDRT)*100/b.allMDRT),1) branchYield from\n" +
            "(select sb.BRANCHSNAME,NVL(a.allMDRT,0)branchAllMDRT  from\n" +
            "(select a.BRANCHSNAME,count(a.BRANCHSNAME)allMDRT from(\n" +
            " select bcb.BRANCHSNAME,bcb.AGENTCODE,NVL(bcb.zcb,0)-NVL(byt.zyt,0) yearInSure from \n" +
            " (select sb.BRANCHSNAME,cb.AGENTCODE,NVL(cb.yearInSure,0) zcb from\n" +
            "(select t.BRANCHCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearInSure from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')   AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.BRANCHCODE)cb RIGHT JOIN\n" +
            "SD_BRANCH sb ON cb.BRANCHCODE=sb.BRANCHCODE)bcb left join\n" +
            "(select sb.BRANCHSNAME,yt.AGENTCODE,NVL(yt.yearYt,0) zyt from\n" +
            "(select t.BRANCHCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearYt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')  AND TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')  AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.Branchcode)yt right join \n" +
            "SD_BRANCH sb ON yt.BRANCHCODE=sb.BRANCHCODE)byt on bcb.AGENTCODE=byt.AGENTCODE)a where a.yearInSure>=#{mdrtTarget} group by a.BRANCHSNAME)a right join\n" +
            "SD_BRANCH sb ON a.BRANCHSNAME=sb.BRANCHSNAME)a,\n" +
            "\n" +
            "(select sb.BRANCHSNAME,NVL(a.allMDRT,0)allMDRT  from\n" +
            "(SELECT a.BRANCHSNAME,count(a.BRANCHSNAME)allMDRT FROM\n" +
            "(select a.BRANCHSNAME,NVL(a.zcb,0)-NVL(b.zyt,0) yearInSure from\n" +
            "(select a.Branchsname,a.AGENTCODE,a.zcb-b.zcb zcb from\n" +
            "(select sb.BRANCHSNAME,cb.AGENTCODE,NVL(cb.yearInSure,0) zcb from\n" +
            "(select t.BRANCHCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearInSure from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')  AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.BRANCHCODE)cb RIGHT JOIN\n" +
            "SD_BRANCH sb ON cb.BRANCHCODE=sb.BRANCHCODE)a,\n" +
            "(select sb.BRANCHSNAME,cb.AGENTCODE,NVL(cb.yearInSure,0) zcb from\n" +
            "(select t.BRANCHCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearInSure from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND TO_CHAR(t.ISSUE_DATE,'q')=TO_CHAR(SYSDATE,'q') AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.BRANCHCODE)cb RIGHT JOIN\n" +
            "SD_BRANCH sb ON cb.BRANCHCODE=sb.BRANCHCODE)b where a.AGENTCODE=b.AGENTCODE)a left join\n" +
            "(select a.BRANCHSNAME,a.AGENTCODE,a.zyt-b.zyt zyt from\n" +
            "(select sb.BRANCHSNAME,yt.AGENTCODE,NVL(yt.yearYt,0) zyt from\n" +
            "(select t.BRANCHCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearYt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')  AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.Branchcode)yt right join \n" +
            "SD_BRANCH sb ON yt.BRANCHCODE=sb.BRANCHCODE)a,\n" +
            "(select sb.BRANCHSNAME,yt.AGENTCODE,NVL(yt.yearYt,0) zyt from\n" +
            "(select t.BRANCHCODE,t.AGENTCODE, SUM(t.WRITTENSTADPREM)yearYt from FACT_YX_PREPREM_LIST_day t where TO_CHAR(t.YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND TO_CHAR(t.ISSUE_DATE,'q')=TO_CHAR(SYSDATE,'q')   AND AGENTSTATE='在职' GROUP BY t.AGENTCODE,t.Branchcode)yt right join \n" +
            "SD_BRANCH sb ON yt.BRANCHCODE=sb.BRANCHCODE)b where a.AGENTCODE=b.AGENTCODE)b on a.AGENTCODE=b.AGENTCODE)a where a.yearInSure>=#{mdrtTarget} GROUP BY a.BRANCHSNAME)a \n" +
            "right join SD_BRANCH sb ON a.BRANCHSNAME=sb.BRANCHSNAME)b\n" +
            "where a.BRANCHSNAME=b.BRANCHSNAME ORDER BY branchIncreaseMDRT desc")
    List<BranchMDRT> getBranchMDRT(@Param("mdrtTarget") Integer mdrtTarget);
}
